﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqliteTest1
{
    public class DeviceTable
    {
        public SQLiteConnection m_dbConnection;
        public DeviceTable(SQLiteConnection sQLiteConnection)
        {
            m_dbConnection = sQLiteConnection;
            CreateTable();
        }

        private void CreateTable()
        {
            string sql = "create table if not exists device (" +
                "ip TEXT not null, " +
                "port INTEGER not null, " +
                "dev TEXT, " +
                "info TEXT, " +
                "lastConnection TEXT," +
                "lastCommand TEXT, " +
                "alarmTime TEXT, " +
                "alarmType INTEGER, " +
                "voltage INTEGER, " +
                "status INTEGER not null, " +
                "primary key (ip, port) " +
                ")";
            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }
        }

        // 插入设备信息
        public int Insert(string ip, int port, string dev, int status)
        {
            string sql = "insert into device (ip, port, dev, status) values ('" +
                ip + "', " +
                port + ", '" +
                dev + "', " +
                status + ")";

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 删除设备
        public int Delete(string ip, int port)
        {
            string sql = "delete from device where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 全选
        public SQLiteDataReader SelectAll()
        {
            string sql = "select * from device";

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteReader();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return null;
        }


        //更新设备状态
        public int UpdataStatus(string ip, int port, int status)
        {
            string sql = "update device set status = " + status + " where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        //获得设备状态
        public int GetStatus(string ip, int port)
        {
            string sql = "select status from devices where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                SQLiteDataReader read = command.ExecuteReader();
                if (read.Read())
                {
                    return read.GetInt32(0);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 更新设备告警

        public int UpdataAlarm(string ip, int port, string alarmTime, int alarmType, int voltage)
        {
            string sql = "update devices " +
                "set " +
                "alarmTime = '" + alarmTime + "', " +
                "alarmType = " + alarmType + ", " +
                "voltage = '" + voltage + "' " +
                " where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        // 更新设备信息

        public int UpdataInfo(string ip, int port, string info)
        {
            string sql = "update devices set info = '" + info + "' where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }

        public int UpdataLastConnection(string ip, int port, string LastConnection, string lastCommand)
        {
            string sql = "update devices set " +
                "LastConnection = '" + LastConnection + "'," +
                "lastCommand = '" + lastCommand + "" +
                "' where " +
                "ip = '" + ip + "' and " +
                "port = " + port;

            try
            {
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                return command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex);
            }

            return 0;
        }
    }
}
